<?php
namespace app\service;
use think\facade\Db;
use app\model\PaymentModel;
use app\model\StockModel;
use app\model\TransferModel;
use Exception;

class PayFlowService{  
    public function list($params){
        $where=[];
        $swhere=[];
        $page = $params['page'];
        $pageCount = $params['page_count'];
        //按出发日期查询
        if(!empty($params['start_date']) && !empty($params['end_date'])){
            if(!empty($params['start_date'])){
                $where[] = ['payment_date','>= time',$params['start_date']]; 
                $swhere[] = ['transfer_date','>= time',$params['start_date']];  
            }
            if(!empty($params['end_date'])){
                $where[] = ['payment_date','<= time',$params['end_date']];  
                $swhere[] = ['transfer_date','<= time',$params['end_date']];  
            }
        }

      
        if(!empty($params['subject_id'])){
            $where[] = ['subject_id','=',$params['subject_id']];  
            $swhere[] = ['from_bank','=',$params['subject_id']];  
        }        
        
        
        $transfer = TransferModel::alias('a')
        ->field('a.transfer_date as price_date,a.from_bank as subject_id,a.from_money as money,a.from_number as invoice_num,a.remark,a.id as supplier_id,a.sn,a.title,a.remark as supplier,c.bank_name as subject')      
        ->join('bank c','a.from_bank=c.id','left')
        ->where($swhere)->buildSql();

        $buy = PaymentModel::alias('a')
        ->field("a.payment_date as price_date,a.subject_id,a.money,a.invoice_num,a.remark,b.supplier_id,b.supplier_id,b.sn,b.content as title,e.supplier_name as supplier,f.bank_name as subject")
        ->join('buy b','a.outlay_id=b.id and a.type=b.type')        
        ->join('supplier e','b.supplier_id=e.id','left')
        ->join('bank f','a.subject_id=f.id','left')
        ->where($where)->buildSql();

        $single = PaymentModel::alias('a')
        ->field("a.payment_date as price_date,a.subject_id,a.money,a.invoice_num,a.remark,b.supplier_id,b.supplier_id,b.sn,b.content as title,e.supplier_name as supplier,f.bank_name as subject")
        ->join('single b','a.outlay_id=b.id and a.type=b.type')        
        ->join('supplier e','b.supplier_id=e.id','left')
        ->join('bank f','a.subject_id=f.id','left')
        ->where($where)->buildSql();       


        $payment = PaymentModel::alias('a')
        ->field("a.payment_date as price_date,a.subject_id,a.money,a.invoice_num,a.remark,b.supplier_id,b.supplier_id,c.sn,c.title,e.supplier_name as supplier,f.bank_name as subject")
        ->join('sale_outlay b','a.outlay_id=b.id and a.type=b.type')
        ->join('sale c','b.pid=c.id')
        ->join('supplier e','b.supplier_id=e.id','left')
        ->join('bank f','a.subject_id=f.id','left')
        ->where($where)->buildSql();       

        $data = Db::table($payment.' a')->union([$transfer,$single,$buy])->page($page,$pageCount)->orderRaw("CAST(invoice_num as decimal)")->order('invoice_num','desc')->select();    
        
        $transferCount = TransferModel::alias('a')
        ->field('a.transfer_date as price_date,a.from_bank as subject_id,a.from_money as money,a.from_number as invoice_num,a.remark,a.id as supplier_id,a.sn,a.title,a.remark as supplier,c.bank_name as subject')      
        ->join('bank c','a.to_bank=c.id','left')
        ->where($swhere)->count(); 

        $buyCount = PaymentModel::alias('a')
            ->field("a.in,a.payment_date as price_date,a.subject_id,a.money,a.invoice_num,a.remark,b.supplier_id as x_id,b.supplier_id,b.sn,b.title")
            ->join('buy b','a.outlay_id=b.id and a.type=b.type')          
            ->join('supplier e','b.supplier_id=e.id','left')
            ->join('bank f','a.subject_id=f.id','left')
            ->where($where)->count(); 

        $singleCount = PaymentModel::alias('a')
            ->field("a.in,a.payment_date as price_date,a.subject_id,a.money,a.invoice_num,a.remark,b.supplier_id as x_id,b.supplier_id,b.sn,b.title")
            ->join('single b','a.outlay_id=b.id and a.type=b.type')          
            ->join('supplier e','b.supplier_id=e.id','left')
            ->join('bank f','a.subject_id=f.id','left')
            ->where($where)->count(); 
        $paymentCount = PaymentModel::alias('a')
            ->field("a.in,a.payment_date as price_date,a.subject_id,a.money,a.invoice_num,a.remark,b.supplier_id as x_id,b.supplier_id,c.sn,c.title")
            ->join('sale_outlay b','a.outlay_id=b.id and a.type=b.type')
            ->join('sale c','b.pid=c.id')
            ->join('supplier e','b.supplier_id=e.id','left')
            ->join('bank f','a.subject_id=f.id','left')
            ->where($where)->count(); 
        return ["list"=>$data,"count"=>$transferCount+$paymentCount+$singleCount+$buyCount];
    } 
}